<?php
  function get_ncc_theo_ten($tenncc) {
  $query = db_select('dt_nha_cung_cap', 'ncc');
  $query->fields('ncc', array('id', 'ma_nha_cung_cap', 'ten_nha_cung_cap', 'so_dien_thoai', 'hien_thi'));
  $query->condition('ncc.ten_nha_cung_cap', $tenncc, '=');
  $query->condition('ncc.hien_thi', 0);
  $data_ten = '';
  $result = $query->execute();
  while ($rs = $result->fetchObject()) {
    $data_ten = $rs->ma_nha_cung_cap;
  }
  return $data_ten;
}

  function get_ncc_theo_sdt($sdt) {
    $query = db_select('dt_nha_cung_cap', 'ncc');
    $query->fields('ncc', array('id', 'ma_nha_cung_cap', 'ten_nha_cung_cap', 'so_dien_thoai', 'hien_thi'));
    $query->condition('ncc.so_dien_thoai', $sdt, '=');
    $query->condition('ncc.hien_thi', 0);
    $result = $query->execute();
    $data_sdt = '';
    while ($rs = $result->fetchObject()) {
      $data_sdt = $rs->ma_nha_cung_cap;
    }
    return $data_sdt;
  }

  function get_ncc_autocomplete($string) {
    $query = db_select('dt_nha_cung_cap', 'ncc')->extend('PagerDefault');
    $query->fields('ncc', array('ma_nha_cung_cap', 'ten_nha_cung_cap', 'so_dien_thoai'));
    $query->condition('ncc.trang_thai', 1);
    $query->condition('ncc.hien_thi', 0);
    $query->condition(
      db_or()->
        condition('ncc.ten_nha_cung_cap', '%' . db_like($string) . '%', 'LIKE')->
        condition('ncc.ma_nha_cung_cap', $string, '=')->
        condition('ncc.ma_nha_cung_cap', 'NCC_' . $string, '=')->
        condition('ncc.so_dien_thoai', $string, '=')
    );
    $result = $query->limit(20)->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[$rs->ten_nha_cung_cap . ' [' . $rs->ma_nha_cung_cap . ']'] = $rs->ten_nha_cung_cap . ' [' . $rs->so_dien_thoai . ']' . ' [' . $rs->ma_nha_cung_cap . ']';
    }
    return $data;
  }

  function get_nha_cung_cap($header = array(), $filter) {
    $query = db_select('dt_nha_cung_cap', 'ncc')->extend('PagerDefault')->extend('TableSort');
    $query->fields('ncc', array('id', 'ma_nha_cung_cap', 'ten_nha_cung_cap', 'trang_thai', 'tien_no', 'dia_chi', 'ghi_chu', 'thoi_gian', 'so_dien_thoai'));
    if ($filter['ma_ref']) {
      $query->condition('ncc.ma_nha_cung_cap', $filter['ma_ref']);
    }
    $query->condition('ncc.hien_thi', 0);
    //  $query->orderBy('ncc.ten_nha_cung_cap','ASC');
    //$query->condition('ncc.trang_thai', $trang_thai);
    $query->orderByHeader($header);
    $result = $query->limit(20)->execute();
    $data = array();
    //$quyen = user_access('lich su giao dich nha cung cap');
    $admin_perm = user_access('admin xoa ncc');
    $edit_perm = user_access('sua nha cung cap');
    while ($rs = $result->fetchObject()) {
      $output = '<a href="nha_cung_cap/danh_sach_nha_cung_cap/xoa_nha_cung_cap/' . $rs->ma_nha_cung_cap . '" onclick="return check_xoa();">Xóa</a>';
      $data[] = array(
        'ma_nha_cung_cap' => $rs->ma_nha_cung_cap,
        'ten_nha_cung_cap' => $rs->ten_nha_cung_cap,
        'tien_no' => number_format($rs->tien_no),
        'so_dien_thoai' => $rs->so_dien_thoai,
        'dia_chi' => $rs->dia_chi,
        // 'thoi_gian' => date('H:i:s d/m/Y', $rs->thoi_gian),
        'thao_tac' => ($admin_perm ? ($output . ' | ')
          : '') . ($edit_perm ? l('Sửa', 'nha_cung_cap/sua_nha_cung_cap/' . $rs->ma_nha_cung_cap)
          : '') . dt_view_ghi_chu('vt_' . $rs->id, 'Nhà cung cấp : ' . $rs->ten_nha_cung_cap, $rs->ghi_chu)
        // . dt_view_ghi_chu('NCC_' . $rs->ma_nha_cung_cap, 'Ghi chú ncc ' . $rs->ten_nha_cung_cap, nl2br($rs->ghi_chu))
//                      (($quyen)
//                        ? ' | ' . l('Lịch sử giao dịch', 'nha_cung_cap/lich_su_giao_dich/gd/' . $rs->ma_nha_cung_cap)
//                        : '') .
//                      (($quyen)
//                        ? ' | ' . l('Lịch sử thanh toán', 'nha_cung_cap/lich_su_giao_dich/tt/' . $rs->ma_nha_cung_cap)
//                        : '')
      );
    }
    return $data;
  }

  function get_tong_cong_no_ncc() {
    $query = db_select('dt_nha_cung_cap', 'ncc');
    $query->addExpression('SUM(tien_no)', 'tong_tien');
    $result = $query->execute();
    while ($rs = $result->fetchObject()) {
      $data[] = array(
        array('data' => 'Tổng công nợ', 'colspan' => 2, 'class' => array('text-center', 'text-bold')),
        array('data' => number_format($rs->tong_tien) . ' VNĐ', 'class' => array('text-bold')),
        //number_format($rs->tong_tien),
      );
    }
    return $data;
  }

  function get_nha_cung_cap_theo_ma($ma_nha_cung_cap) {
    $query = db_select('dt_nha_cung_cap', 'ncc');
    $query->fields('ncc', array('id', 'ma_nha_cung_cap', 'ten_nha_cung_cap', 'dia_chi', 'so_dien_thoai', 'ghi_chu','tien_no'));
    $query->condition('ncc.ma_nha_cung_cap', $ma_nha_cung_cap, '=');
    $result = $query->execute()->fetchObject();
    return $result;
  }

  function insert_nha_cung_cap($uid, $ten_nha_cung_cap, $dia_chi, $so_dien_thoai, $ghi_chu, $tien_no) {
    $trang_thai = 1;
    $thoi_gian = time();
    $query = db_insert('dt_nha_cung_cap');
    $arr = array(
      'uid' => $uid,
      'trang_thai' => $trang_thai,
      'ma_nha_cung_cap' => '',
      'ten_nha_cung_cap' => $ten_nha_cung_cap,
      'dia_chi' => $dia_chi,
      'so_dien_thoai' => $so_dien_thoai,
      'tien_no' => $tien_no,
      'ghi_chu' => $ghi_chu,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_nha_cung_cap = $id;
    while (strlen($ma_nha_cung_cap) < 3) {
      $ma_nha_cung_cap = '0' . $ma_nha_cung_cap;
    }
    $ma_nha_cung_cap = 'NCC_' . $ma_nha_cung_cap;
    db_update('dt_nha_cung_cap')
      ->fields(array(
                    'ma_nha_cung_cap' => $ma_nha_cung_cap,
               ))
      ->condition('id', $id)
      ->execute();
    $query_n = db_insert('dt_nha_cung_cap_du_lieu');
    $arr_n = array(
      'uid' => $uid,
      'ma_nha_cung_cap' => $ma_nha_cung_cap,
      'tien_no' => $tien_no,
      'thoi_gian' => $thoi_gian
    );
    $query_n->fields(array_keys($arr_n));
    $query_n->values($arr_n);
    $query_n->execute();
    return $ma_nha_cung_cap;
  }

  function update_nha_cung_cap($ten_nha_cung_cap, $dia_chi, $so_dien_thoai, $ghi_chu, $ma_nha_cung_cap) {
    db_update('dt_nha_cung_cap')
      ->fields(array(
                    'ten_nha_cung_cap' => $ten_nha_cung_cap,
                    'dia_chi' => $dia_chi,
                    'so_dien_thoai' => $so_dien_thoai,
                    'ghi_chu' => $ghi_chu,
               ))
      ->condition('ma_nha_cung_cap', $ma_nha_cung_cap)
      ->execute();
  }

  function update_nha_cung_cap_admin($ten_nha_cung_cap, $dia_chi, $so_dien_thoai, $ghi_chu, $ma_nha_cung_cap, $tien_no) {
    db_update('dt_nha_cung_cap')
      ->fields(array(
                    'ten_nha_cung_cap' => $ten_nha_cung_cap,
                    'dia_chi' => $dia_chi,
                    'tien_no' => $tien_no,
                    'so_dien_thoai' => $so_dien_thoai,
                    'ghi_chu' => $ghi_chu,
               ))
      ->condition('ma_nha_cung_cap', $ma_nha_cung_cap)
      ->execute();
  }

  function update_trang_thai_nha_cung_cap($trang_thai, $ma_nha_cung_cap) {
    return db_update('dt_nha_cung_cap')
      ->fields(array(
                    'trang_thai' => $trang_thai,
               ))
      ->condition('ma_nha_cung_cap', $ma_nha_cung_cap)
      ->execute();
  }

  function update_tien_no_nha_cung_cap($sign, $tien_no, $ma_nha_cung_cap) {
    db_update('dt_nha_cung_cap')
      ->expression('tien_no', 'tien_no ' . $sign . ' :tien_no', array(':tien_no' => $tien_no))
      ->condition('ma_nha_cung_cap', $ma_nha_cung_cap)
      ->execute();
  }

  function get_phieu_nhap_vat_tu($header, $ma_ncc) {
    $query_x = db_select('dt_vat_tu_phieu_nhap', 'p');
    $query_x->fields('p', array('id', 'trang_thai', 'thoi_gian', 'tong_tien'));
    $query_x->addField('p', 'ma_phieu_nhap', 'ma_phieu');
    $query_x->leftJoin('dt_nha_cung_cap', 'ref', 'ref.ma_nha_cung_cap = p.ma_nha_cung_cap');
    $query_x->addField('ref', 'ten_nha_cung_cap');
    $query_x->addExpression("'Nhập vật tư'", 'loai');
    $query_x->addExpression("'nhap'", 'link');
    $query_x->condition('p.ma_nha_cung_cap', $ma_ncc);
    $query_n = db_select('dt_de_c_phieu_xuat', 'px');
    $query_n->fields('px', array('id', 'trang_thai', 'thoi_gian', 'tong_tien'));
    $query_n->addField('px', 'ma_phieu_xuat', 'ma_phieu');
    $query_n->leftJoin('dt_nha_cung_cap', 'ref1', 'ref1.ma_nha_cung_cap = px.ma_khach_hang');
    $query_n->addField('ref1', 'ten_nha_cung_cap');
    $query_n->addExpression("'Xuất đề C'", 'loai');
    $query_n->addExpression("'xuatdec'", 'link');
    $query_n->condition('px.ma_khach_hang', $ma_ncc);
    $query_t = db_select('dt_vat_tu_xuat_hoi', 'h');
    $query_t->fields('h', array('id', 'trang_thai', 'thoi_gian', 'tong_tien'));
    $query_t->addField('h', 'ma_phieu_xuat_hoi', 'ma_phieu');
    $query_t->leftJoin('dt_nha_cung_cap', 'ref1', 'ref1.ma_nha_cung_cap = h.ma_nha_cung_cap');
    $query_t->addField('ref1', 'ten_nha_cung_cap');
    $query_t->addExpression("'Hồi vật tư'", 'loai');
    $query_t->addExpression("'hoi'", 'link');
    $query_t->condition('h.ma_nha_cung_cap', $ma_ncc);
    $query_x->union($query_n, 'UNION ALL');
    $query_x->union($query_t, 'UNION ALL');
    $query = db_select($query_x, 'vt')->extend('PagerDefault')->extend('TableSort');
    ;
    $query->fields('vt', array('ma_phieu', 'trang_thai', 'thoi_gian', 'tong_tien', 'ten_nha_cung_cap', 'loai', 'link'));
    $query->orderByHeader($header);
    $result = $query->orderBy('thoi_gian', 'DESC')->limit(20)->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[] = array(
        'ma_phieu_nhap' => $rs->ma_phieu,
        'ten_nha_cung_cap' => $rs->ten_nha_cung_cap,
        'tien_no' => number_format($rs->tong_tien),
        'thoi_gian' => date('H:i:s d/m/Y', $rs->thoi_gian),
        'loai' => $rs->loai,
        'thao_tac' => l('Chi tiết', 'vat_tu/phieu_vat_tu/in_phieu/' . $rs->link . '/' . $rs->ma_phieu)
      );
    }
    return $data;
  }

  function get_phieu_chi_vat_tu($header, $ma_ncc) {
    $query = db_select('dt_phieu_chi_vat_tu', 'cvt')->extend('PagerDefault')->extend('TableSort');
    $query->fields('cvt', array('id', 'ma_phieu_chi', 'ma_nha_cung_cap', 'ma_thu_quy', 'tong_tien', 'nguoi_nhan', 'thoi_gian'));
    $query->condition('cvt.ma_nha_cung_cap', $ma_ncc);
    $query->leftJoin('dt_nha_cung_cap', 'ncc', 'ncc.ma_nha_cung_cap = cvt.ma_nha_cung_cap');
    $query->addField('ncc', 'ten_nha_cung_cap');
    $query->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = cvt.ma_thu_quy');
    $query->addField('tq', 'ten_thu_quy');
    $query->orderByHeader($header);
    $result = $query->orderBy('cvt.thoi_gian', 'DESC')->limit(20)->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[] = array(
        'ma_phieu_chi' => $rs->ma_phieu_chi,
        'ma_nha_cung_cap' => $rs->ten_nha_cung_cap,
        'ma_thu_quy' => $rs->ten_thu_quy,
        'tong_tien' => number_format($rs->tong_tien),
        'nguoi_nhan' => $rs->nguoi_nhan,
        'thoi_gian' => date('H:i:s d/m/Y', $rs->thoi_gian),
        'thao_tac' => l('Chi tiết', 'quy_doanh_nghiep/phieu_thu_chi/in_phieu/chivattu/' . $rs->ma_phieu_chi)
      );
    }
    return $data;
  }

  function get_thong_ke_ncc_ngay($start_date, $end_date, $start_month, $end_month) {
    $query = db_select('dt_nha_cung_cap', 'ncc');
    $query->fields('ncc', array('id', 'ma_nha_cung_cap', 'ten_nha_cung_cap', 'thoi_gian'));
    $query->addExpression('(SELECT SUM(n0.tong_tien) FROM {dt_vat_tu_phieu_nhap} AS n0 WHERE ncc.ma_nha_cung_cap = n0.ma_nha_cung_cap AND n0.thoi_gian < :n0_start_month)', 'nhap_den_thang', array(':n0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(h0.tong_tien) FROM {dt_vat_tu_xuat_hoi} AS h0 WHERE ncc.ma_nha_cung_cap = h0.ma_nha_cung_cap AND h0.thoi_gian < :h0_start_month)', 'hoi_den_thang', array(':h0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(x0.tong_tien) FROM {dt_de_c_phieu_xuat} AS x0 WHERE ncc.ma_nha_cung_cap = x0.ma_khach_hang AND x0.thoi_gian < :x0_start_month)', 'dec_den_thang', array(':x0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(c0.tong_tien) FROM {dt_phieu_chi_vat_tu} AS c0 WHERE ncc.ma_nha_cung_cap = c0.ma_nha_cung_cap AND c0.thoi_gian < :c0_start_month)', 'chi_den_thang', array(':c0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(t0.tong_tien) FROM {dt_phieu_thu_le} AS t0 WHERE ncc.ma_nha_cung_cap = t0.ma_nha_cung_cap AND t0.thoi_gian < :t0_start_month)', 'thu_den_thang', array(':t0_start_month' => $start_month));
    $query->addExpression('(SELECT d.tien_no FROM {dt_nha_cung_cap_du_lieu} AS d WHERE ncc.ma_nha_cung_cap = d.ma_nha_cung_cap AND d.thoi_gian < :d_start_month)', 'du', array(':d_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(n1.tong_tien) FROM {dt_vat_tu_phieu_nhap} AS n1 WHERE ncc.ma_nha_cung_cap = n1.ma_nha_cung_cap AND n1.thoi_gian >= :n1_start_month AND n1.thoi_gian <= :n1_end_month)', 'nhap_thang', array(':n1_start_month' => $start_month, ':n1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(h1.tong_tien) FROM {dt_vat_tu_xuat_hoi} AS h1 WHERE ncc.ma_nha_cung_cap = h1.ma_nha_cung_cap AND h1.thoi_gian >= :h1_start_month AND h1.thoi_gian <= :h1_end_month)', 'hoi_thang', array(':h1_start_month' => $start_month, ':h1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(x1.tong_tien) FROM {dt_de_c_phieu_xuat} AS x1 WHERE ncc.ma_nha_cung_cap = x1.ma_khach_hang AND x1.thoi_gian >= :x1_start_month AND x1.thoi_gian <= :x1_end_month)', 'dec_thang', array(':x1_start_month' => $start_month, ':x1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(c1.tong_tien) FROM {dt_phieu_chi_vat_tu} AS c1 WHERE ncc.ma_nha_cung_cap = c1.ma_nha_cung_cap AND c1.thoi_gian >= :c1_start_month AND c1.thoi_gian <= :c1_end_month)', 'chi_thang', array(':c1_start_month' => $start_month, ':c1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(t1.tong_tien) FROM {dt_phieu_thu_le} AS t1 WHERE ncc.ma_nha_cung_cap = t1.ma_nha_cung_cap AND t1.thoi_gian >= :t1_start_month AND t1.thoi_gian <= :t1_end_month)', 'thu_thang', array(':t1_start_month' => $start_month, ':t1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(n2.tong_tien) FROM {dt_vat_tu_phieu_nhap} AS n2 WHERE ncc.ma_nha_cung_cap = n2.ma_nha_cung_cap AND n2.thoi_gian >= :n2_start_date AND n2.thoi_gian <= :n2_end_date)', 'nhap_ngay', array(':n2_start_date' => $start_date, ':n2_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(h2.tong_tien) FROM {dt_vat_tu_xuat_hoi} AS h2 WHERE ncc.ma_nha_cung_cap = h2.ma_nha_cung_cap AND h2.thoi_gian >= :h2_start_date AND h2.thoi_gian <= :h2_end_date)', 'hoi_ngay', array(':h2_start_date' => $start_date, ':h2_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(x2.tong_tien) FROM {dt_de_c_phieu_xuat} AS x2 WHERE ncc.ma_nha_cung_cap = x2.ma_khach_hang AND x2.thoi_gian >= :x2_start_date AND x2.thoi_gian <= :x2_end_date)', 'dec_ngay', array(':x2_start_date' => $start_date, ':x2_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(c2.tong_tien) FROM {dt_phieu_chi_vat_tu} AS c2 WHERE ncc.ma_nha_cung_cap = c2.ma_nha_cung_cap AND c2.thoi_gian >= :c2_start_date AND c2.thoi_gian <= :c2_end_date)', 'chi_ngay', array(':c2_start_date' => $start_date, ':c2_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(t2.tong_tien) FROM {dt_phieu_thu_le} AS t2 WHERE ncc.ma_nha_cung_cap = t2.ma_nha_cung_cap AND t2.thoi_gian >= :t2_start_date AND t2.thoi_gian <= :t2_end_date)', 'thu_ngay', array(':t2_start_date' => $start_date, ':t2_end_date' => $end_date));
    $result = $query->orderBy('ncc.ten_nha_cung_cap')->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[] = array(
        //l($rs->ten_vat_tu . ' (' . $rs->ma_vat_tu . ')', 'vat_tu/thong_ke_vat_tu/chi_tiet_vat_tu/den_ngay/' . $rs->ma_vat_tu),
        array('data' => $rs->ten_nha_cung_cap . '[' . $rs->ma_nha_cung_cap . ']', 'class' => array('text-center')),
        array('data' => number_format($rs->nhap_den_thang + $rs->thu_den_thang - $rs->hoi_den_thang - $rs->dec_den_thang - $rs->chi_den_thang + $rs->du), 'class' => array('text-center')),
        $rs->nhap_thang
          ? l(number_format($rs->nhap_thang), 'nha_cung_cap/danh_sach_nha_cung_cap/chi_tiet_phieu/nhap/den_ngay/' . $rs->ma_nha_cung_cap)
          : '',
        $rs->chi_thang
          ? l(number_format($rs->chi_thang), 'nha_cung_cap/danh_sach_nha_cung_cap/chi_tiet_phieu/thanhtoan/den_ngay/' . $rs->ma_nha_cung_cap)
          : '',
        $rs->hoi_thang
          ? l(number_format($rs->hoi_thang), 'nha_cung_cap/danh_sach_nha_cung_cap/chi_tiet_phieu/hoi/den_ngay/' . $rs->ma_nha_cung_cap)
          : '',
        $rs->dec_thang
          ? l(number_format($rs->dec_thang), 'nha_cung_cap/danh_sach_nha_cung_cap/chi_tiet_phieu/dec/den_ngay/' . $rs->ma_nha_cung_cap)
          : '',
        $rs->thu_thang
          ? l(number_format($rs->thu_thang), 'nha_cung_cap/danh_sach_nha_cung_cap/chi_tiet_phieu/thule/den_ngay/' . $rs->ma_nha_cung_cap)
          : '',
        $rs->nhap_ngay
          ? l(number_format($rs->nhap_ngay), 'nha_cung_cap/danh_sach_nha_cung_cap/chi_tiet_phieu/nhap/trong_ngay/' . $rs->ma_nha_cung_cap)
          : '',
        $rs->chi_ngay
          ? l(number_format($rs->chi_ngay), 'nha_cung_cap/danh_sach_nha_cung_cap/chi_tiet_phieu/thanhtoan/trong_ngay/' . $rs->ma_nha_cung_cap)
          : '',
        $rs->hoi_ngay
          ? l(number_format($rs->hoi_ngay), 'nha_cung_cap/danh_sach_nha_cung_cap/chi_tiet_phieu/hoi/trong_ngay/' . $rs->ma_nha_cung_cap)
          : '',
        $rs->dec_ngay
          ? l(number_format($rs->dec_ngay), 'nha_cung_cap/danh_sach_nha_cung_cap/chi_tiet_phieu/dec/trong_ngay/' . $rs->ma_nha_cung_cap)
          : '',
        $rs->thu_ngay
          ? l(number_format($rs->thu_ngay), 'nha_cung_cap/danh_sach_nha_cung_cap/chi_tiet_phieu/thule/trong_ngay/' . $rs->ma_nha_cung_cap)
          : '',
//        array('data' => number_format($rs->nhap_thang), 'class' => array('text-center')),
//        array('data' => number_format($rs->chi_thang), 'class' => array('text-center')),
//        array('data' => number_format($rs->hoi_thang), 'class' => array('text-center')),
//        array('data' => number_format($rs->dec_thang), 'class' => array('text-center')),
//
//        array('data' => number_format($rs->nhap_ngay), 'class' => array('text-center')),
//        array('data' => number_format($rs->chi_ngay), 'class' => array('text-center')),
//        array('data' => number_format($rs->hoi_ngay), 'class' => array('text-center')),
//        array('data' => number_format($rs->dec_ngay), 'class' => array('text-center')),
      );
    }
    return $data;
  }

  function get_thong_ke_ncc_thang($start_month, $end_month) {
    $query = db_select('dt_nha_cung_cap', 'ncc');
    $query->fields('ncc', array('id', 'ma_nha_cung_cap', 'ten_nha_cung_cap', 'thoi_gian'));
    $query->condition('ncc.hien_thi', 0);
    $query->addExpression('(SELECT SUM(n0.tong_tien) FROM {dt_vat_tu_phieu_nhap} AS n0 WHERE ncc.ma_nha_cung_cap = n0.ma_nha_cung_cap AND n0.thoi_gian < :n0_start_month)', 'nhap_den_thang', array(':n0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(h0.tong_tien) FROM {dt_vat_tu_xuat_hoi} AS h0 WHERE ncc.ma_nha_cung_cap = h0.ma_nha_cung_cap AND h0.thoi_gian < :h0_start_month)', 'hoi_den_thang', array(':h0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(x0.tong_tien) FROM {dt_de_c_phieu_xuat} AS x0 WHERE ncc.ma_nha_cung_cap = x0.ma_khach_hang AND x0.thoi_gian < :x0_start_month)', 'dec_den_thang', array(':x0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(c0.tong_tien) FROM {dt_phieu_chi_vat_tu} AS c0 WHERE ncc.ma_nha_cung_cap = c0.ma_nha_cung_cap AND c0.thoi_gian < :c0_start_month)', 'chi_den_thang', array(':c0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(t0.tong_tien_chua_giam) FROM {dt_phieu_thu_le} AS t0 WHERE ncc.ma_nha_cung_cap = t0.ma_nha_cung_cap AND t0.thoi_gian < :t0_start_month)', 'thu_den_thang', array(':t0_start_month' => $start_month));
    $query->addExpression('(SELECT d.tien_no FROM {dt_nha_cung_cap_du_lieu} AS d WHERE ncc.ma_nha_cung_cap = d.ma_nha_cung_cap AND d.thoi_gian < :d_start_month)', 'du', array(':d_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(ps0.so_tien) FROM {dt_nha_cung_cap_phat_sinh} AS ps0 WHERE ncc.ma_nha_cung_cap = ps0.ma_khach_hang AND ps0.thoi_gian < :ps0_start_month)', 'ps_den_thang', array(':ps0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(n1.tong_tien) FROM {dt_vat_tu_phieu_nhap} AS n1 WHERE ncc.ma_nha_cung_cap = n1.ma_nha_cung_cap AND n1.thoi_gian >= :n1_start_month AND n1.thoi_gian <= :n1_end_month)', 'nhap_thang', array(':n1_start_month' => $start_month, ':n1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(h1.tong_tien) FROM {dt_vat_tu_xuat_hoi} AS h1 WHERE ncc.ma_nha_cung_cap = h1.ma_nha_cung_cap AND h1.thoi_gian >= :h1_start_month AND h1.thoi_gian <= :h1_end_month)', 'hoi_thang', array(':h1_start_month' => $start_month, ':h1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(x1.tong_tien) FROM {dt_de_c_phieu_xuat} AS x1 WHERE ncc.ma_nha_cung_cap = x1.ma_khach_hang AND x1.thoi_gian >= :x1_start_month AND x1.thoi_gian <= :x1_end_month)', 'xuat_thang', array(':x1_start_month' => $start_month, ':x1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(c1.tong_tien) FROM {dt_phieu_chi_vat_tu} AS c1 WHERE ncc.ma_nha_cung_cap = c1.ma_nha_cung_cap AND c1.thoi_gian >= :c1_start_month AND c1.thoi_gian <= :c1_end_month)', 'chi_thang', array(':c1_start_month' => $start_month, ':c1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(t1.tong_tien_chua_giam) FROM {dt_phieu_thu_le} AS t1 WHERE ncc.ma_nha_cung_cap = t1.ma_nha_cung_cap AND t1.thoi_gian >= :t1_start_month AND t1.thoi_gian <= :t1_end_month)', 'thu_thang', array(':t1_start_month' => $start_month, ':t1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(ps1.so_tien) FROM {dt_nha_cung_cap_phat_sinh} AS ps1 WHERE ncc.ma_nha_cung_cap = ps1.ma_khach_hang AND ps1.thoi_gian >= :ps1_start_month AND ps1.thoi_gian <= :ps1_end_month)', 'ps_thang', array(':ps1_start_month' => $start_month, ':ps1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(n2.tong_tien) FROM {dt_vat_tu_phieu_nhap} AS n2 WHERE ncc.ma_nha_cung_cap = n2.ma_nha_cung_cap AND n2.thoi_gian < :n2_start_month)', 'nhap_cuoi_thang', array(':n2_start_month' => $end_month));
    $query->addExpression('(SELECT SUM(h2.tong_tien) FROM {dt_vat_tu_xuat_hoi} AS h2 WHERE ncc.ma_nha_cung_cap = h2.ma_nha_cung_cap AND h2.thoi_gian < :h2_start_month)', 'hoi_cuoi_thang', array(':h2_start_month' => $end_month));
    $query->addExpression('(SELECT SUM(x2.tong_tien) FROM {dt_de_c_phieu_xuat} AS x2 WHERE ncc.ma_nha_cung_cap = x2.ma_khach_hang AND x2.thoi_gian < :x2_start_month)', 'dec_cuoi_thang', array(':x2_start_month' => $end_month));
    $query->addExpression('(SELECT SUM(c2.tong_tien) FROM {dt_phieu_chi_vat_tu} AS c2 WHERE ncc.ma_nha_cung_cap = c2.ma_nha_cung_cap AND c2.thoi_gian < :c2_start_month)', 'chi_cuoi_thang', array(':c2_start_month' => $end_month));
    $query->addExpression('(SELECT SUM(t2.tong_tien_chua_giam) FROM {dt_phieu_thu_le} AS t2 WHERE ncc.ma_nha_cung_cap = t2.ma_nha_cung_cap AND t2.thoi_gian < :t2_start_month)', 'thu_cuoi_thang', array(':t2_start_month' => $end_month));
    $query->addExpression('(SELECT SUM(ps2.so_tien) FROM {dt_nha_cung_cap_phat_sinh} AS ps2 WHERE ncc.ma_nha_cung_cap = ps2.ma_khach_hang AND ps2.thoi_gian < :ps2_start_month)', 'ps_cuoi_thang', array(':ps2_start_month' => $end_month));
    $result = $query->orderBy('ncc.ten_nha_cung_cap')->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[] = array(
        //l($rs->ten_vat_tu . ' (' . $rs->ma_vat_tu . ')', 'vat_tu/thong_ke_vat_tu/chi_tiet_vat_tu/trong_thang/' . $rs->ma_vat_tu),
        array('data' => $rs->ten_nha_cung_cap . '[' . $rs->ma_nha_cung_cap . ']', 'class' => array('text-center')),
        number_format($rs->nhap_den_thang + $rs->thu_den_thang - $rs->hoi_den_thang - $rs->dec_den_thang - $rs->chi_den_thang + $rs->du + $rs->ps_den_thang),
        $rs->nhap_thang
          ? l(number_format($rs->nhap_thang), 'nha_cung_cap/danh_sach_nha_cung_cap/chi_tiet_phieu/nhap/trong_thang/' . $rs->ma_nha_cung_cap)
          : '',
        $rs->chi_thang
          ? l(number_format($rs->chi_thang), 'nha_cung_cap/danh_sach_nha_cung_cap/chi_tiet_phieu/thanhtoan/trong_thang/' . $rs->ma_nha_cung_cap)
          : '',
        $rs->hoi_thang
          ? l(number_format($rs->hoi_thang), 'nha_cung_cap/danh_sach_nha_cung_cap/chi_tiet_phieu/hoi/trong_thang/' . $rs->ma_nha_cung_cap)
          : '',
        $rs->xuat_thang
          ? l(number_format($rs->xuat_thang), 'nha_cung_cap/danh_sach_nha_cung_cap/chi_tiet_phieu/dec/trong_thang/' . $rs->ma_nha_cung_cap)
          : '',
        $rs->thu_thang
          ? l(number_format($rs->thu_thang), 'nha_cung_cap/danh_sach_nha_cung_cap/chi_tiet_phieu/thule/trong_thang/' . $rs->ma_nha_cung_cap)
          : '',
        $rs->ps_thang
          ? l(number_format($rs->ps_thang), 'nha_cung_cap/danh_sach_nha_cung_cap/chi_tiet_phieu/phatsinh/trong_thang/' . $rs->ma_nha_cung_cap)
          : '',
        number_format($rs->nhap_cuoi_thang + $rs->thu_cuoi_thang - $rs->hoi_cuoi_thang - $rs->dec_cuoi_thang - $rs->chi_cuoi_thang + $rs->du + $rs->ps_cuoi_thang),
//        array('data' => number_format($rs->nhap_thang), 'class' => array('text-center')),
//        array('data' => number_format($rs->chi_thang), 'class' => array('text-center')),
//        array('data' => number_format($rs->hoi_thang), 'class' => array('text-center')),
//        array('data' => number_format($rs->xuat_thang), 'class' => array('text-center')),
      );
    }
    return $data;
  }

  function get_thong_ke_chi_tiet_ncc($ncc, $type, $start_time, $end_time) {
    switch ($type) {
      case 'nhap':
        $query = db_select('dt_vat_tu_phieu_nhap', 'p')->extend('PagerDefault');
        $query->condition('p.ma_nha_cung_cap', $ncc->ma_nha_cung_cap, '=');
        $query->condition('p.thoi_gian', $start_time, '>=');
        $query->condition('p.thoi_gian', $end_time, '<=');
        $query->leftJoin('dt_nha_cung_cap', 'ref', 'ref.ma_nha_cung_cap = p.ma_nha_cung_cap');
        $query->fields('p', array('id', 'trang_thai', 'thoi_gian', 'tong_tien'));
        $query->addField('p', 'ma_phieu_nhap', 'ma_phieu');
        $query->addField('ref', 'ten_nha_cung_cap', 'ref');
        $result = $query->orderBy('p.thoi_gian', 'DESC')->limit(20)->execute();
        $data = array();
        $header = array(
          'Mã phiếu',
          'Nhà cung cấp',
          'Số tiền',
          'Trạng thái',
          'Ngày tháng',
          'Thao tác'
        );
        while ($rs = $result->fetchObject()) {
          $data[] = array(
            array('data' => $rs->ma_phieu, 'class' => array('')),
            array('data' => $rs->ref, 'class' => array('')),
            array('data' => number_format($rs->tong_tien), 'class' => array('')),
            array('data' => $rs->trang_thai ? 'Đã khớp' : 'Chưa khớp', 'class' => array('')),
            array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('')),
            l('Chi tiết', 'vat_tu/phieu_vat_tu/in_phieu/nhap/' . $rs->ma_phieu)
          );
        }
        break;
      case 'thanhtoan':
        $query = db_select('dt_phieu_chi_vat_tu', 'cvt')->extend('PagerDefault')->extend('TableSort');
        $query->condition('cvt.ma_nha_cung_cap', $ncc->ma_nha_cung_cap, '=');
        $query->condition('cvt.thoi_gian', $start_time, '>=');
        $query->condition('cvt.thoi_gian', $end_time, '<=');
        $query->fields('cvt', array('id', 'ma_phieu_chi', 'ma_nha_cung_cap', 'ma_thu_quy', 'tong_tien', 'nguoi_nhan', 'thoi_gian'));
        $query->leftJoin('dt_nha_cung_cap', 'ncc', 'ncc.ma_nha_cung_cap = cvt.ma_nha_cung_cap');
        $query->addField('ncc', 'ten_nha_cung_cap');
        $query->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = cvt.ma_thu_quy');
        $query->addField('tq', 'ten_thu_quy');
        $result = $query->orderBy('cvt.thoi_gian', 'DESC')->limit(20)->execute();
        $data = array();
        $header = array(
          'Mã phiếu',
          'Nhà cung cấp',
          'Thủ quỹ',
          'Số tiền',
          'Người nhận',
          'Ngày tháng',
          'Thao tác'
        );
        while ($rs = $result->fetchObject()) {
          $data[] = array(
            array('data' => $rs->ma_phieu_chi, 'class' => array('')),
            array('data' => $rs->ten_nha_cung_cap, 'class' => array('')),
            array('data' => $rs->ten_thu_quy, 'class' => array('')),
            array('data' => number_format($rs->tong_tien), 'class' => array('')),
            array('data' => $rs->nguoi_nhan, 'class' => array('')),
            array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('')),
            l('Chi tiết', 'quy_doanh_nghiep/phieu_thu_chi/in_phieu/chivattu/' . $rs->ma_phieu_chi)
          );
        }
        break;
      case 'hoi':
        $query = db_select('dt_vat_tu_xuat_hoi', 'p')->extend('PagerDefault');
        $query->condition('p.ma_nha_cung_cap', $ncc->ma_nha_cung_cap, '=');
        $query->condition('p.thoi_gian', $start_time, '>=');
        $query->condition('p.thoi_gian', $end_time, '<=');
        $query->leftJoin('dt_nha_cung_cap', 'ref', 'ref.ma_nha_cung_cap = p.ma_nha_cung_cap');
        $query->fields('p', array('id', 'trang_thai', 'thoi_gian', 'tong_tien'));
        $query->addField('p', 'ma_phieu_xuat_hoi', 'ma_phieu');
        $query->addField('ref', 'ten_nha_cung_cap', 'ref');
        $result = $query->orderBy('p.thoi_gian', 'DESC')->limit(20)->execute();
        $data = array();
        $header = array(
          'Mã phiếu',
          'Nhà cung cấp',
          'Số tiền',
          'Trạng thái',
          'Ngày tháng',
          'Thao tác'
        );
        while ($rs = $result->fetchObject()) {
          $data[] = array(
            array('data' => $rs->ma_phieu, 'class' => array('')),
            array('data' => $rs->ref, 'class' => array('')),
            array('data' => number_format($rs->tong_tien), 'class' => array('')),
            array('data' => $rs->trang_thai ? 'Đã khớp' : 'Chưa khớp', 'class' => array('')),
            array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('')),
            l('Chi tiết', 'vat_tu/phieu_vat_tu/in_phieu/hoi/' . $rs->ma_phieu)
          );
        }
        break;
      case 'dec':
        $query = db_select('dt_de_c_phieu_xuat', 'p')->extend('PagerDefault');
        $query->condition('p.ma_khach_hang', $ncc->ma_nha_cung_cap, '=');
        $query->condition('p.thoi_gian', $start_time, '>=');
        $query->condition('p.thoi_gian', $end_time, '<=');
        $query->fields('p', array('id', 'ten_khach_hang', 'la_khach_hang', 'tong_tien', 'trang_thai', 'thoi_gian'));
        $query->addField('p', 'ma_phieu_xuat', 'ma_phieu');
        $result = $query->orderBy('p.thoi_gian', 'DESC')->limit(20)->execute();
        $data = array();
        $header = array(
          'Mã phiếu',
          'Loại',
          'Họ tên',
          'Số tiền',
          'Trạng thái',
          'Ngày tháng',
          'Thao tác'
        );
        while ($rs = $result->fetchObject()) {
          $data[] = array(
            array('data' => $rs->ma_phieu, 'class' => array('')),
            array('data' => $rs->la_khach_hang ? 'Khách lẻ' : 'Nhà cung cấp', 'class' => array('')),
            array('data' => $rs->ten_khach_hang, 'class' => array('')),
            array('data' => number_format($rs->tong_tien), 'class' => array('')),
            array('data' => $rs->trang_thai ? 'Đã khớp' : 'Chưa khớp', 'class' => array('')),
            array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('')),
            l('Chi tiết', 'vat_tu/phieu_vat_tu/in_phieu/xuatdec/' . $rs->ma_phieu)
          );
        }
        break;
      case 'thule':
        $query = db_select('dt_phieu_thu_le', 'tl')->extend('PagerDefault')->extend('TableSort');
        $query->fields('tl', array('id', 'ma_phieu_thu', 'ma_thu_quy', 'tong_tien_chua_giam', 'ma_nha_cung_cap', 'tong_tien', 'tien_bot', 'nguoi_nop', 'thanh_toan', 'thoi_gian'));
        $query->condition('tl.ma_nha_cung_cap', $ncc->ma_nha_cung_cap, '=');
        $query->condition('tl.thoi_gian', $start_time, '>=');
        $query->condition('tl.thoi_gian', $end_time, '<=');
        $query->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = tl.ma_thu_quy');
        $query->addField('tq', 'ten_thu_quy');
        $result = $query->orderBy('tl.thoi_gian', 'DESC')->limit(15)->execute();
        $data = array();
        $header = array(
          'Mã phiếu thu',
          'Số tiền trước giảm',
          'Số tiền thu',
          'Tiền bớt',
          'Thủ quỹ thu',
          'Thanh toán',
          'Ngày tháng',
          'Thao tác'
        );
        while ($rs = $result->fetchObject()) {
          $data[] = array(
            array('data' => $rs->ma_phieu_thu, 'class' => array('text-center')),
            array('data' => number_format($rs->tong_tien_chua_giam), 'class' => array('text-center')),
            array('data' => number_format($rs->tong_tien), 'class' => array('text-center')),
            array('data' => number_format($rs->tien_bot), 'class' => array('text-center')),
            array('data' => $rs->ten_thu_quy, 'class' => array('text-center')),
            array('data' => $rs->thanh_toan ? 'Đã chuyển tiền' : 'Chưa chuyển tiền', 'class' => array('text-center')),
            array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('text-center')),
            l('Chi tiết', 'quy_doanh_nghiep/phieu_thu_chi/in_phieu/' . $type . '/' . $rs->ma_phieu_thu),
          );
        }
        break;
      case 'phatsinh':
        $query = db_select('dt_nha_cung_cap_phat_sinh', 'ps')->extend('PagerDefault')->extend('TableSort');
        $query->fields('ps', array('id', 'ma_phieu_phat_sinh', 'ma_khach_hang', 'so_tien', 'ghi_chu', 'thoi_gian'));
        $query->condition('ps.ma_khach_hang', $ncc->ma_nha_cung_cap, '=');
        $query->condition('ps.thoi_gian', $start_time, '>=');
        $query->condition('ps.thoi_gian', $end_time, '<=');
        $result = $query->orderBy('ps.thoi_gian', 'DESC')->limit(15)->execute();
        $data = array();
        $header = array(
          'Mã phiếu phát sinh',
          'Số tiền',
          'Lý do',
          'Ngày tháng',
          'Thao tác'
        );
        while ($rs = $result->fetchObject()) {
          $data[] = array(
            array('data' => $rs->ma_phieu_phat_sinh, 'class' => array('text-center')),
            array('data' => number_format($rs->so_tien), 'class' => array('text-center')),
            array('data' => $rs->ghi_chu, 'class' => array('text-center')),
            array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('text-center')),
            l('Chi tiết', 'vat_tu/phieu_vat_tu/in_phieu/phatsinh/' . $rs->ma_phieu_phat_sinh),
          );
        }
        break;
    }
    return array('header' => $header, 'rows' => $data);
  }

  function insert_phat_sinh_cong_no($uid, $ma_nha_cung_cap, $so_tien, $ghi_chu) {
    $thoi_gian = time();
    $query = db_insert('dt_nha_cung_cap_phat_sinh');
    $arr = array(
      'uid' => $uid,
      'ma_phieu_phat_sinh' => '',
      'ma_khach_hang' => $ma_nha_cung_cap,
      'so_tien' => $so_tien,
      'ghi_chu' => $ghi_chu,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_phieu_thu = $id;
    while (strlen($ma_phieu_thu) < 3) {
      $ma_phieu_thu = '0' . $ma_phieu_thu;
    }
    $ma_phieu_thu = 'PPS_' . $ma_phieu_thu;
    db_update('dt_nha_cung_cap_phat_sinh')
      ->fields(array(
                    'ma_phieu_phat_sinh' => $ma_phieu_thu,
               ))
      ->condition('id', $id)
      ->execute();
    return $ma_phieu_thu;
  }

  function xoa_nha_cung_cap($ma_ncc) {
    db_update('dt_nha_cung_cap')
      ->fields(array(
                    'hien_thi' => 1,
               ))
      ->condition('ma_nha_cung_cap', $ma_ncc)
      ->execute();
  }

